package datasources.dbdatasource;

import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import org.jopac2.utils.BookSignature;

//import JOpac2.dataModules.RecordFactory;
//import JOpac2.dataModules.RecordInterface;

import bookhive.datasource.DataSource;
import bookhive.datastructures.datasource.DataItem;
import bookhive.datastructures.datasource.HashItem;

public class MySQLDataSource implements DataSource {

	private static final String ENCODING = "UTF-8";
	private Vector<HashItem> hashresult;
	private Vector<DataItem> dataresult;
	private Connection connection = null;

	public MySQLDataSource() {
		initConnection();
		initDB();
		hashresult = new Vector<HashItem>();
		dataresult = new Vector<DataItem>();
	}

	private void initDB() {
		String recordsTable = "CREATE TABLE IF NOT EXISTS `bookhive`.`records` ("
				+ "`ID` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,"
				+ "`hash` VARCHAR(45) NOT NULL,"
				+ "`data` TEXT NOT NULL,"
				+ "`type` VARCHAR(45) NOT NULL,"
				+ "PRIMARY KEY(`ID`) ) ENGINE = MyISAM;";

		String signsTable = "CREATE TABLE IF NOT EXISTS `bookhive`.`signs` ("
				+ "`ID` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,"
				+ "`hash` VARCHAR(45) NOT NULL,"
				+ "`LibName` VARCHAR(100) NOT NULL DEFAULT ' ',"
				+ "`LibCode` VARCHAR(45) NOT NULL DEFAULT ' ',"
				+ "`BookNum` VARCHAR(45) NOT NULL DEFAULT ' ',"
				+ "`BookLoc` VARCHAR(45) NOT NULL DEFAULT ' ',"
				+ "`Cons` VARCHAR(45) NOT NULL DEFAULT ' ',"
				+ "PRIMARY KEY(`ID`),"
				+ "INDEX `Index_2`(`hash`) ) ENGINE = MyISAM;";

		try {
			Statement st = connection.createStatement();
			st.execute(recordsTable);
			st.execute(signsTable);
			st.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	private void initConnection() {

		try {
			// Load the JDBC driver
			String driverName = "com.mysql.jdbc.Driver"; // MySQL MM JDBC
															// driver
			Class.forName(driverName);

			// Create a connection to the database
			String serverName = "localhost";
			String mydatabase = "bookhive";
			String url = "jdbc:mysql://" + serverName + "/" + mydatabase; // a
																			// JDBC
																			// url
			String username = "root";
			String password = "%op01rt!";
			connection = DriverManager.getConnection(url, username, password);
		} catch (ClassNotFoundException e) {
		} catch (SQLException e) {
		}

	}

	public Vector<HashItem> getHashResult() {
		return hashresult;
	}

	public Vector<DataItem> getDataResult() {
		return dataresult;
	}

	public synchronized Vector<HashItem> sendHashQuery(String query) {

		hashresult.clear();

		try {
			query = URLDecoder.decode(query, ENCODING);
		} catch (UnsupportedEncodingException e1) {
			e1.printStackTrace();
		}

		String[] querys = query.split("&");
		String attr = querys[0];
		String[] attrs = attr.split("=");

		String q = "Select * from signs where hash in (SELECT records.hash FROM records where records.data LIKE '%"
				+ attrs[1] + "%')";

		try {

			Statement st = connection.createStatement();
			ResultSet rs = st.executeQuery(q);
			while (rs.next()) {
				HashItem hi = new HashItem();
				hi.id = rs.getLong("ID");
				hi.hash = rs.getString("hash");
				String libname = rs.getString("LibName");
				String libcode = rs.getString("LibCode");
				String booknum = rs.getString("BookNum");
				String bookloc = rs.getString("BookLoc");
				String cons = rs.getString("Cons");
				BookSignature bs = new BookSignature(libcode, libname, booknum,
						bookloc, cons);
				hi.signs.add(bs);
				hashresult.add(hi);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return hashresult;

	}

	public synchronized Vector<DataItem> sendDataQuery(String hash) {

		dataresult.clear();

		String query = "select * from records where hash='" + hash + "'";

		try {
			Statement st = connection.createStatement();
			ResultSet rs = st.executeQuery(query);
			while (rs.next()) {
				DataItem di = new DataItem();
				di.id = rs.getLong("ID");
				di.type = rs.getString("type");
				di.data = rs.getString("data");
				dataresult.add(di);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return dataresult;
	}

	public synchronized boolean sendUpdateQuery(String queryData) {

		boolean ok = false;
		try {
			queryData = URLDecoder.decode(queryData, ENCODING);
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}

		try {
			PreparedStatement ps = connection
					.prepareStatement("insert into signs(hash,LibName,LibCode,BookNum,BookLoc,Cons) values(?,?,?,?,?,?)");

			String[] params = queryData.split("&");
			for (int i = 0; i < params.length; i++) {
				String[] param = params[i].split("=");

				if (param[0].equals("hash"))
					ps.setString(1, param[1]);
				else if (param[0].equals("LID"))
					ps.setString(3, param[1]);
				else if (param[0].equals("LNA"))
					ps.setString(2, param[1]);
				else if (param[0].equals("BNO"))
					ps.setString(4, param[1]);
				else if (param[0].equals("BLO"))
					ps.setString(5, param[1]);
				else if (param[0].equals("BCO"))
					ps.setString(6, param.length < 2 ? "" : param[1]);
			}

			ok = ps.execute();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println("Update queryData: " + queryData);
		return ok;
	}

	public synchronized boolean sendRedundQuery(String queryData) {

		boolean ok = false;
		
		try {
			queryData = URLDecoder.decode(queryData, ENCODING);
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}

		try {
			PreparedStatement ps = connection
					.prepareStatement("insert into records(hash,data,type) values(?,?,?)");

			String[] params = queryData.split("&");
			for (int i = 0; i < params.length; i++) {
				String[] param = params[i].split("=");

				if (param[0].equals("hash"))
					ps.setString(1, param[1]);
				else if (param[0].equals("data"))
					ps.setString(2, param[1]);
				else if (param[0].equals("type"))
					ps.setString(3, param[1]);
			}

			ok = ps.execute();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println("Update queryData: " + queryData);
		return ok;
	}

	public HashItem getRandomRecord() {
		HashItem ret = new HashItem();
		long max = getMaxID();
		long id = (long) (Math.random() * max);

		try {
			String q = "select * from records where id=" + id;
			Statement st = connection.createStatement();
			ResultSet rs = st.executeQuery(q);
			if (rs.next()) {
				ret.id = id;
				ret.hash = rs.getString("hash");
				ret.type = rs.getString("type");
			}
			rs.close();

			q = "select * from signs where hash='" + ret.hash + "'";
			rs = st.executeQuery(q);
			while (rs.next()) {
				BookSignature bs = new BookSignature(rs.getString("LibCode"),
						rs.getString("LibName"), rs.getString("BookNum"), rs
								.getString("BookLoc"), rs.getString("Cons"));
				ret.signs.add(bs);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ret;
	}

	private long getMaxID() {
		long r = 0;
		String q = "select max(ID) from records";
		try {
			Statement st = connection.createStatement();
			ResultSet rs = st.executeQuery(q);
			if (rs.next())
				r = rs.getLong(1);
			rs.close();
			st.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return r;
	}

//	private boolean dbContains(BookSignature bs) {
//		boolean ret = false;
//		try {
//			String q = "select * from signs where LibName=? and LibCode=? and BookNum=? and BookLoc=? and Cons=?";
//			PreparedStatement ps = connection.prepareStatement(q);
//			ps.setString(1, bs.getLibraryName());
//			ps.setString(2, bs.getLibraryId());
//			ps.setString(3, bs.getBookNumber());
//			ps.setString(4, bs.getBookLocalization());
//			ps.setString(5, bs.getBookCons());
//			ResultSet rs = ps.executeQuery();
//			if (rs.next())
//				ret = true;
//			rs.close();
//			ps.close();
//		} catch (Exception e) {
//		}
//		return ret;
//	}
//
//	private synchronized RecordInterface getFromDb(String hash) {
//		RecordInterface ret = null;
//		Vector<DataItem> div = sendDataQuery(hash);
//		if (div.size() > 0) {
//			DataItem di = div.firstElement();
//			ret = RecordFactory.buildRecord(di.id, di.data, di.type, 0);
//		}
//		return ret;
//	}

}
